﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace BusinessLayer
{
    public class Loan
    {

        public string Title { get; set; }
        public string ISBN { get; set; }
        public string Barcode { get; set; }
        public string StartDate{ get; set;}
        public string EndDate { get; set; }
        public string ReturnDate { get; set; }

        public static void extendLoan(string persID, string barcode)
        {
            string sql = "UPDATE BORROW SET ToBeReturnedDate = DATEADD(day, 30, ToBeReturnedDate) WHERE PersonId = @PID AND Barcode = @Barcode";

            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.Add(new SqlParameter("PID", persID));
            cmd.Parameters.Add(new SqlParameter("Barcode", barcode));

            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }
        }

        public static List<Loan> getActiveLoans(string persID)
        {
            List<Loan> result = new List<Loan>();
            string sql = "SELECT BORROW.Barcode, BORROW.BorrowDate, BORROW.ToBeReturnedDate, COPY.Barcode, BOOK.ISBN, BOOK.Title " +
                "FROM BORROWER INNER JOIN BORROW ON BORROWER.PersonId = BORROW.PersonId INNER JOIN COPY ON BORROW.Barcode = COPY.Barcode INNER JOIN BOOK ON COPY.ISBN = BOOK.ISBN " +
                "WHERE (BORROWER.PersonId=@PID) AND (BORROW.ReturnDate IS NULL)";

            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.Add(new SqlParameter("PID", persID));
            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                while (dar.Read())
                {
                    Loan newLoan = new Loan();
                    newLoan.Barcode = dar["Barcode"] as string;
                    newLoan.ISBN = dar["ISBN"] as string;
                    newLoan.Title = dar["Title"] as string;
                    DateTime sts = (DateTime)dar["BorrowDate"];
                    newLoan.StartDate = sts.ToString("dd/MM/yy"); 
                    DateTime end = (DateTime)dar["ToBeReturnedDate"];
                    newLoan.EndDate = end.ToString("dd/MM/yyyy");
                    result.Add(newLoan);
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }

            return result;
        }

        public static List<Loan> getLoanHistory(string persID)
        {
            List<Loan> result = new List<Loan>();
            string sql = "SELECT BORROW.Barcode, BORROW.BorrowDate, BORROW.ToBeReturnedDate, BORROW.ReturnDate, COPY.Barcode, BOOK.ISBN, BOOK.Title " +
                "FROM BORROWER INNER JOIN BORROW ON BORROWER.PersonId = BORROW.PersonId INNER JOIN COPY ON BORROW.Barcode = COPY.Barcode INNER JOIN BOOK ON COPY.ISBN = BOOK.ISBN " +
                "WHERE (BORROWER.PersonId=@PID) AND (BORROW.ReturnDate IS NOT NULL)";

            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.Add(new SqlParameter("PID", persID));
            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                while (dar.Read())
                {
                    Loan newLoan = new Loan();
                    newLoan.Barcode = dar["Barcode"] as string;
                    newLoan.ISBN = dar["ISBN"] as string;
                    newLoan.Title = dar["Title"] as string;
                    DateTime start = (DateTime)dar["BorrowDate"];
                    newLoan.StartDate = start.ToString("dd/MM/yyyy");
                    DateTime end = (DateTime)dar["ToBeReturnedDate"];
                    newLoan.EndDate = end.ToString("dd/MM/yyyy");
                    DateTime rtrn = (DateTime)dar["ReturnDate"];
                    newLoan.ReturnDate = rtrn.ToString("dd/MM/yyyy");
                    result.Add(newLoan);
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }

            return result;
        }
    }
}
